package com.dp.appi.dao.impl;

import com.dp.appi.dao.ProjectStatisticsDao;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by bchen on 2016/5/23.
 */
@Component
public class ProjectStatisticsDaoImpl extends BaseDaoImpl implements ProjectStatisticsDao {
    /**
     * 个人项目进度统计
     * id
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, java.lang.Object>> scheduleCount(Object id, Object org_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select IF(a.finish_time is null,0,1) finish,count(1) num ");
        sql.append("from p_stage a left join p_baseinfo b on a.project_id = b.project_id where b.cost_center = ? and a.deleted = 0 and ");
        sql.append("(a.manager like '%" + id + "%' OR ");
        sql.append("a.explorationer like '%" + id + "%' OR ");
        sql.append("a.designer like '%" + id + "%' OR ");
        sql.append("a.auditor like '%" + id + "%' ) ");
        sql.append("GROUP BY IF(a.finish_time is null,0,1) ");
        return getJdbcTemplate().queryForList(sql.toString(), org_id);
    }

    /**
     * 阶段线路杆塔明细
     *
     * @param voltageGrade
     * @param stageId
     * @return
     */
    @Override
    public List<Map<String, Object>> poleSum(String voltageGrade, Long stageId, int classify) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.number,pole_type,name codeset,pole_attribute ");
        sql.append("from p_exploration a ");
        sql.append("left JOIN a_pole b ");
        sql.append("on a.pt_objectid = b.id ");
        sql.append("LEFT JOIN c_codeset c ");
        sql.append("on a.codeset_id = c.codeset_id ");
        sql.append("where a.stage_id = ? and a.pt_id = 100004 ");

        if (voltageGrade != null && !voltageGrade.equals("")) {
            sql.append("and a.voltage_grade = " + voltageGrade);
        }

        sql.append(" and a.deleted = 0 and a.classify = ? ");
        sql.append("ORDER BY number;");
        return getJdbcTemplate().queryForList(sql.toString(), stageId, classify);
    }

    /**
     * 阶段台区材料统计
     *
     * @param explor_id
     * @return
     */
    @Override
    public List<Map<String, Object>> materialSum(Long stage_id, Integer classify, Long explor_id, String exploration_type) {
        StringBuffer sb1 = new StringBuffer();
        StringBuilder sb2 = new StringBuilder();
//        sql.append(" select name,material_number,spec,unit,sum(num) num  ");
//        sql.append(" from p_exploration_material_sum a  ");
//        sql.append(" INNER JOIN p_exploration b ");
//        sql.append(" on (a.exploration_id = b.exploration_id and b.exploration_type= "+exploration_type);
//        sql.append(" and b.classify="+classify);
//        sql.append(" and b.deleted=0 and b.stage_id ="+stage_id );
//        sql.append(" )");
//        sql.append(" where b.deleted =0 ");


//        select
//        p.name,p.material_number,p.spec,p.unit,sum(p.singlLength) as num
//        from xl_material p
//        UNION ALL
//        select name,material_number,spec,unit,sum(num) num
//        from p_exploration_material_sum a
//        INNER JOIN p_exploration b
//        on (a.exploration_id = b.exploration_id )
//        if (null != explor_id) {
//            if (explor_id == -1) {
//                sql.append(" and b.voltage_grade =101 ");
//            } else {
//                sql.append(" and b.explor_id = " + explor_id);
//            }
//        }
//        sql.append("  GROUP BY name,material_number,spec,unit");
        sb1.append(" select t1.name,t1.material_number,t1.spec,t1.unit,round(sum(t1.num),1) as num");
        sb1.append("  from p_exploration_material_sum t1");
        sb1.append(" left join c_material_category t2 on t1.small_category = t2.material_category_id");
        sb1.append(" inner join p_exploration t3 on t1.exploration_id = t3.exploration_id");
        sb1.append(" left join c_material_category t5 on t1.big_category = t5.material_category_id");
        sb1.append(" left join c_material_category t6 on t1.middle_category = t6.material_category_id");
        sb1.append(" where t3.stage_id =" + stage_id);
        sb1.append(" and t3.classify=" + classify);
        sb1.append(" and t3.exploration_type=" + exploration_type);
        if (null != explor_id) {
            if (explor_id == -1) {
                sb1.append(" AND t3.voltage_grade = 101 ");
            } else {
                sb1.append(" and t3.explor_id = " + explor_id);
            }
        }
        sb1.append(" GROUP BY t1.name,t1.spec");
        List<Map<String, Object>> list1 = getJdbcTemplate().queryForList(sb1.toString());


        sb2.append(" select p.name,p.material_number,p.spec,p.unit,ROUND(sum(p.singlLength),1) AS num");
        sb2.append("  from xl_material p");
        sb2.append(" INNER JOIN p_exploration pex on p.exploration_id= pex.exploration_id");
        sb2.append(" where p.stage_id =" + stage_id);
        sb2.append(" and p.classify=" + classify);
        sb2.append(" and p.exploration_type=" + exploration_type);
        if (null != explor_id) {
            if (explor_id == -1) {
                sb2.append(" and p.voltage_grade =101 ");
            } else {
                sb2.append(" and pex.explor_id = " + explor_id);
            }
        }
        sb2.append(" GROUP BY p.name,p.spec");
        List<Map<String, Object>> list2 = getJdbcTemplate().queryForList(sb2.toString());
        if (null != list2)
            for (int i = 0; i < list2.size(); i++) {
                list1.add(list2.get(i));
            }

        return list1;
    }

    /**
     * 阶段用电户
     *
     * @param stage_id
     * @return
     */
    @Override
    public List<Map<String, Object>> userPoint(Long stage_id, int classify) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT (@i:=@i+1) `index`,a.number,b.user_name,b.all_user ");
        sql.append("from (select @i:=0) c,p_exploration a ");
        sql.append("INNER JOIN a_user_point b ");
        sql.append("on a.pt_objectid = b.id ");
        sql.append("where a.deleted = 0 and a.pt_id = 100005 and a.stage_id = ? and a.classify = ? and a.exploration_type = 11");
        return getJdbcTemplate().queryForList(sql.toString(), new Object[]{stage_id, classify});
    }

    /**
     * 项目设计阶段统计
     *
     * @param id 开始年份：startYear
     *           结束年份：endYear
     *           项目属性：project_attribute
     *           项目类型：project_type
     *           资金来源：funds_sources
     *           设计公司：design_unit
     *           业主单位：owner_unit
     * @return
     */
    @Override
    public List<Map<String, Object>> projectSum(Map<String, String> param, String id, String groupBy, Object
            org_id) {
        String gas = "stage";
        String gb = "a.stage ";
        if (null != groupBy && !"".equals(groupBy)) {
            gb = groupBy;
            gas = "code";
        }

        String startYear = param.get("startYear");
        String endYear = param.get("endYear");
        String project_attribute = param.get("project_attribute");
        String project_type = param.get("project_type");
        String funds_sources = param.get("funds_sources");
        String design_unit = param.get("design_unit");
        String owner_unit = param.get("owner_unit");
        String stage = param.get("stage");

        StringBuffer sql = new StringBuffer();

        sql.append("select IFNULL(" + gb + ",0) " + gas + ",count(1) num from p_stage a ");
        sql.append(" INNER JOIN p_baseinfo b ");
        sql.append("on a.project_id = b.project_id ");
        sql.append("where b.cost_center='" + org_id + "' and (a.manager like '%" + id + "%' ");
        sql.append("or a.explorationer like '%" + id + "%' ");
        sql.append("or a.designer like '%" + id + "%' ");
        sql.append("or a.auditor like '%" + id + "%') ");
        sql.append("and a.deleted = 0 and b.deleted = 0 ");

        List lp = new ArrayList();
//        lp.add(org_id);
        if (null != startYear && !"".equals(startYear)) {
            lp.add(startYear);
            sql.append("and ? <= DATE_FORMAT(a.create_time ,'%Y') ");
        }
        if (null != endYear && !"".equals(endYear)) {
            lp.add(endYear);
            sql.append("and ? >= DATE_FORMAT(a.finish_time ,'%Y') ");
        }
        if (null != project_attribute && !"".equals(project_attribute)) {
            lp.add(project_attribute);
            sql.append("and b.project_attribute = ? ");
        }
        if (null != project_type && !"".equals(project_type)) {
            lp.add(project_type);
            sql.append("and b.project_type = ? ");
        }
        if (null != funds_sources && !"".equals(funds_sources)) {
            lp.add(funds_sources);
            sql.append("and b.funds_sources = ? ");
        }
        if (null != design_unit && !"".equals(design_unit)) {
            lp.add(design_unit);
            sql.append("and a.design_unit = ? ");
        }
        if (null != owner_unit && !"".equals(owner_unit)) {
            lp.add(owner_unit);
            sql.append("and a.owner_unit = ? ");
        }
        if (null != stage && !"".equals(stage)) {
            lp.add(stage);
            sql.append("and a.stage = ? ");
        }
        sql.append("GROUP BY IFNULL(" + gb + ", 0) ");

        Object obj[] = new Object[lp.size()];
        for (int i = 0; i < lp.size(); i++) {
            obj[i] = lp.get(i);
        }
        return getJdbcTemplate().queryForList(sql.toString(), obj);
    }

    /**
     * 交叉跨越描述
     *
     * @param explorId
     * @return
     */
    @Override
    public String describe(Long stage_id, Integer classify, Long explorId, String exploration_type) {
        StringBuffer sql = new StringBuffer();
        sql.append("select CONCAT('共',sum(num),'处，其中', ");
        sql.append("group_concat(dis)) `describe`  from ( ");
        sql.append("select CONCAT(d.`name`,count(1),d.unit) dis,count(1) num  from p_channel a ");
        sql.append("left join p_exploration b ");
        sql.append("on a.exploration_id = b.exploration_id ");
        sql.append("INNER join p_exploration c on");
        sql.append(" (a.feature_id = c.exploration_id and c.exploration_type=" + exploration_type + " and c.classify=" + classify + " and c.deleted=0 and c.stage_id =" + stage_id + ")");
        sql.append(" LEFT JOIN p_pt d on c.pt_id = d.pt_id ");
        sql.append(" where b.deleted = 0 ");

        if (null != explorId) {
            if (explorId == -1) {
                sql.append(" and c.voltage_grade =101 ");
            } else {
                sql.append(" and b.explor_id =  " + explorId);
            }
        }
        sql.append(" GROUP BY d.`name` ) t1 ");
        return queryForObject(sql.toString(), String.class);
    }


    /**
     * 交叉跨越列表
     *
     * @param stage_id
     * @return
     */
    @Override
    public List<Map<String, Object>> corssList(Long stage_id, Integer classify, Long explor_id, String
            exploration_type) {
        StringBuffer sql = new StringBuffer();
        sql.append("select section,group_concat(corss) corss from ( ");
        sql.append("select CONCAT(IFNULL(e.number,'无'),'至',b.number ) section,");
        sql.append("CONCAT(count(1),c.unit,c.`name`) corss ");
        sql.append("from p_channel a ");
        sql.append("INNER join p_exploration p on");
        sql.append(" (a.feature_id = p.exploration_id and p.exploration_type=" + exploration_type + " and p.classify=" + classify + " and p.deleted=0 and p.stage_id =" + stage_id + ")");
        sql.append("left join p_exploration b ");
        sql.append("on a.exploration_id = b.exploration_id ");
        sql.append("left join p_exploration d ");
        sql.append("on a.feature_id = d.exploration_id ");
        sql.append("left join p_pt c ");
        sql.append("on d.pt_id = c.pt_id ");
        sql.append("LEFT JOIN p_exploration e ");
        sql.append("on d.before_exp = e.exploration_id ");
        sql.append(" where b.deleted = 0 and d.deleted = 0 ");

        if (null != explor_id) {
            if (explor_id == -1) {
                sql.append(" and b.voltage_grade =101 ");
            } else {
                sql.append(" and b.explor_id =  " + explor_id);
            }
        }
        sql.append(" and (e.deleted = 0 or e.deleted is null) ");
        sql.append(" group by CONCAT(IFNULL(e.number,'无'),'至',b.number ),c.`name` ");
        sql.append(" ) t group by section ");
//        sql.append("ORDER BY section");
        return getJdbcTemplate().queryForList(sql.toString());
    }

    /**
     * 阶段交叉跨越
     *
     * @param stage_id
     * @return
     */
    @Override
    public List<Map<String, Object>> pointSum(Long stage_id, int classify) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.pt_id,if(a.pt_id=100005,sum(b.user_num), ");
        sql.append("count(1)) num ");
        sql.append("from p_exploration a ");
        sql.append("LEFT JOIN a_user_point b ");
        sql.append("on a.pt_objectid = b.id ");
        sql.append("where a.pt_id in (100002,100004,100005) ");
        sql.append("and stage_id = ? ");
        sql.append("and a.classify = ? ");
        sql.append("and a.deleted =0 ");
        sql.append(" and a.exploration_type=11");
        sql.append(" GROUP BY a.pt_id ");
        return getJdbcTemplate().queryForList(sql.toString(), stage_id, classify);
    }

    /**
     * 项目阶段台区统计
     *
     * @param explor_id
     * @return
     */
    @Override
    public Map<String, Object> explorSum(Long explor_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select b.trans_capacity,count(1) num ");
        sql.append(" from p_exploration a ");
        sql.append(" left join a_transformer b ");
        sql.append(" on a.pt_objectid = b.id ");
        sql.append(" left join p_exploration d ");
        sql.append(" on a.exploration_id = d.explor_id ");
        sql.append(" and d.pt_id = 100004 ");
        sql.append(" where a.exploration_id = ? and ");
        sql.append(" a.deleted = 0 and d.deleted = 0 ");
        return findOneForJdbc(sql.toString(), explor_id);
    }

    /**
     * 按电压等级统计里程、杆塔数量
     *
     * @param explorList
     * @return
     */
    @Override
    public List<Map<String, Object>> routeList(List<Map<String, Object>> explorList) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.voltage_grade,ROUND(sum(a.distance)/1000,2) line_length, ");
        sql.append("sum(IF(a.pt_id=100004,1,0)) line_pole ");
        sql.append("From p_exploration a where a.deleted = 0 ");
        Object[] param = new Object[explorList.size()];
        for (int i = 0; null != explorList && i < explorList.size(); i++) {
            Map<String, Object> explor = explorList.get(i);
            Long explor_id = Long.valueOf(String.valueOf(explor.get("exploration_id")));
            param[i] = explor_id;
            if (i == 0) {
                sql.append(" and ( ");
            } else {
                sql.append(" or ");
            }
            sql.append("FIND_IN_SET(a.exploration_id, queryExpChildrenList(?)) ");
            if (i == explorList.size() - 1) {
                sql.append(" ) ");
            }
        }

        sql.append("GROUP BY a.voltage_grade ");
        return getJdbcTemplate().queryForList(sql.toString(), param);
    }

    /**
     * 按电压等级统计里程、杆塔数量
     *
     * @param explor_id
     * @return
     */
    @Override
    public List<Map<String, Object>> routeList1(Long explor_id) {
//        StringBuffer sql = new StringBuffer();
//        sql.append("select a.voltage_grade,ROUND(sum(a.distance)/1000,2) line_length, ");
//        sql.append("sum(IF(a.pt_id=100004,1,0)) line_pole ");
//        sql.append("From p_exploration a ");
//        sql.append("LEFT JOIN p_pt b ");
//        sql.append("on a.pt_id = b.pt_id ");
//        sql.append(" where a.deleted = 0 ");
//        sql.append(" and b.pt_type = 11 ");
//        sql.append(" and a.explor_id = ? ");
//        sql.append(" GROUP BY a.voltage_grade");
//        return getJdbcTemplate().queryForList(sql.toString(), explor_id);
        List<Map<String, Object>> list = new ArrayList<>();
        list.add(this.routeListByV_bt_102(102, explor_id));
        list.add(this.routeListByV_bt_102(103, explor_id));
        return list;
    }


    private Map<String, Object> routeListByV_bt_102(int voltage_grade, Long explor_id) {
        String sql = "select p1.voltage_grade,round(sum(IFNULL(p1.distance,0)/1000),3) as line_length,sum(if(p1.pt_id=100004,1,0)) as line_pole\n" +
                "                    from p_exploration p1\n" +
                "                    INNER JOIN p_exploration p2 on p1.before_exp = p2.exploration_id\n" +
                "                    INNER JOIN p_pt p3 on (p1.pt_id = p3.pt_id and p3.pt_type=11)\n" +
                "                    where p1.pt_id in(100004,100011,100012) and p1.deleted = 0 \n" +
                "                    and p1.exploration_type=11 and  p1.voltage_grade = ?";
        if (null != explor_id)
            sql += " and p1.explor_id=" + explor_id;
        return getJdbcTemplate().queryForMap(sql, new Object[]{voltage_grade});
    }

    /**
     * 按电压等级统计里程、杆塔数量
     *
     * @param stage_id
     * @param classify
     * @return
     */
    @Override
    public List<Map<String, Object>> routeListByV(Long stage_id, int classify, Long explor_id) {
//        StringBuffer sql = new StringBuffer();
//        sql.append("SELECT a.voltage_grade, ");
//        sql.append(" ROUND(sum(a.distance)/1000, 2) line_length, ");
//        sql.append(" sum(IF(a.pt_id = 100004, 1, 0)) line_pole ");
//        sql.append(" FROM p_exploration a ");
//        sql.append(" LEFT JOIN p_pt b ");
//        sql.append(" on a.pt_id = b.pt_id ");
//        sql.append(" WHERE a.deleted = 0 ");
//        sql.append(" AND a.stage_id = ? ");
//        sql.append(" AND a.classify = ? ");
//        sql.append(" AND b.pt_type = 11 ");
//        sql.append("GROUP BY a.voltage_grade ");
//        return getJdbcTemplate().queryForList(sql.toString(), stage_id, classify);
        List<Map<String, Object>> list = new ArrayList<>();
        if (null == explor_id) {//查询全部
            list.add(this.routeListByV_101(stage_id, classify, 101));
//            list.add(this.routeListByV_102(stage_id, classify, 102, explor_id));
//            list.add(this.routeListByV_102(stage_id, classify, 103, explor_id));
            list.add(this.routeListByV_102(stage_id, classify, 102, explor_id));
            list.add(this.routeListByV_102(stage_id, classify, 103, explor_id));
        } else {
            if (explor_id == -1) {//查询10kv
                list.add(this.routeListByV_101(stage_id, classify, 101));
            } else {//查询某台区
                list.add(this.routeListByV_102(stage_id, classify, 102, explor_id));
                list.add(this.routeListByV_102(stage_id, classify, 103, explor_id));
            }
        }
        return list;
    }

    /**
     * 10kv线路长度及杆塔统计
     *
     * @param stage_id
     * @param classify
     * @return
     */
    private Map<String, Object> routeListByV_101(Long stage_id, int classify, int voltage_grade) {
        String sql = "select pp.voltage_grade,round(sum(IFNULL(pp.distance,0)/1000),3) as line_length,sum(if(pp.pt_id=100004,1,0)) as line_pole from (\n" +
                "                    select p1.pt_id,p1.distance,p1.deleted,p1.classify,p1.stage_id,p1.exploration_type,p1.voltage_grade from p_exploration p1\n" +
                "                    LEFT JOIN p_exploration p2 on p1.before_exp = p2.exploration_id\n" +
                "                    INNER JOIN p_pt p3 on (p1.pt_id = p3.pt_id and p3.pt_type=11)\n" +
                "                    where p1.pt_id = 100004\n" +
                "                    union ALL\n" +
                "                    select p1.pt_id,p1.distance,p1.deleted,p1.classify,p1.stage_id,p1.exploration_type,p1.voltage_grade from p_exploration p1\n" +
                "                    INNER JOIN p_exploration p2 on p1.before_exp = p2.exploration_id\n" +
                "                    INNER JOIN p_pt p3 on (p1.pt_id = p3.pt_id and p3.pt_type=11)\n" +
                "                    where p1.pt_id=100002 and p2.pt_id=100004) pp\n" +
                "                    where pp.deleted = 0 and pp.stage_id = ? and pp.classify = ? and pp.exploration_type=11\n" +
                "                     and pp.voltage_grade = ?";
        return getJdbcTemplate().queryForMap(sql, new Object[]{stage_id, classify, voltage_grade});
    }

    /**
     * 0.38kv线路长度及杆塔统计
     *
     * @param stage_id
     * @param classify
     * @return
     */
    private Map<String, Object> routeListByV_102(Long stage_id, int classify, int voltage_grade, Long explor_id) {
        String sql = "select p1.voltage_grade,round(sum(IFNULL(p1.distance,0)/1000),3) as line_length,sum(if(p1.pt_id=100004,1,0)) as line_pole\n" +
                "                    from p_exploration p1\n" +
                "                    left JOIN p_exploration p2 on p1.before_exp = p2.exploration_id\n" +
                "                    INNER JOIN p_pt p3 on (p1.pt_id = p3.pt_id and p3.pt_type=11)\n" +
                "                    where p1.pt_id in(100004,100011,100012) and p1.deleted = 0 and p1.stage_id = ? and p1.classify = ? \n" +
                "                    and p1.exploration_type=11 and  p1.voltage_grade = ?";
        if (null != explor_id)
            sql += " and p1.explor_id=" + explor_id;
        return getJdbcTemplate().queryForMap(sql, new Object[]{stage_id, classify, voltage_grade});
    }

}
